#-*-coding:utf-8 -*-
'''
Created on 2013-6-28

@author: Administrator
'''

import time
import logging
import MySQLdb

target_host = '127.0.0.1'
target_port = 3307
target_user = 'root'
target_passwd = '860728'

logging.basicConfig(filename = r'F:/workspace/mypython/log.txt' ,level = logging.INFO)
INSERT_SIZE = 500
# logging.basicConfig(level = logging.INFO)
class Tree(object):
    
    def __init__(self):
        self.tree = {}
    
    def addSender(self, sender):
        if sender.startswith('+86'):
            sender = sender[3:]
        
        if len(sender) <= 9:
            
            self.__getNode(sender)
        else:
            fs = sender[0:9]
            node = self.__getNode(fs)
            l = 11
            if len(sender) > 11:
                l = 11
            ss = sender[9:l]
            for s in ss:
                sNode = self.__getChildNode(node, s)
                node = sNode
            if len(sender) > 11:
                self.__getChildNode(node, sender[l:])
                
    def __getChildNode(self, parent, s):
        if parent.has_key('children'):
            node = parent['children']
        else:
            node = {}
            parent['children'] = node
        if node.has_key(s):
            child = node[s]
        else:
            child = {'count':0}
            node[s] = child
        child['count'] += 1
        return child
    
    def __getNode(self, sender):
        node = None
        if self.tree.has_key(sender):
            node = self.tree[sender]
        else:
            node = {'count':0, 'sender': sender}
            self.tree[sender] = node
        node['count'] += 1
        return node
    
    def sort(self):
        keysort = lambda node: node['count'];
        return sorted(self.tree.values(), key=keysort,reverse=True)
    
def checktable(cursor, tablename):
    cursor.execute('show tables like %s', [tablename])
    row = cursor.fetchone()
    if row:
        return True
    else :
        return False

def createtable(cursor, tablename):
    sql = 'create table %s like secure_sms_statisc' % tablename
    cursor.execute(sql)

def main():
    smsFile = open(r'F:/workspace/mypython/sms20130626.txt', 'r')
    result = open(r'F:/workspace/mypython/sms20130626_result.txt', 'w')
    tree = Tree()
    
    start = time.time()
    
    lines = smsFile.readlines()
    i = 0;
    for sender in lines:
        tree.addSender(sender.strip('\r').strip('\n').strip())
        i += 1
        if i > 100000:
            break
    
    logging.info('construct tree time: %f' % (time.time() - start))
    
    start = time.time()
    nodes = tree.sort()
    logging.info('sort tree time: %f' % (time.time() - start))
    
    start = time.time()
    
    target_db = MySQLdb.connect(host = target_host, port = target_port, user = target_user, passwd = target_passwd, db='work')
    target_cursor = target_db.cursor()
    tablename = 'secure_sms_statisc%s' % time.strftime('%Y%m%d')
    if not checktable(target_cursor, tablename):
        createtable(target_cursor, tablename)
    logging.info("create table times: %f" % (time.time() - start))
    
    start = time.time()
    start_2 = time.time()
    l = len(nodes)
    sindex = 0
    eindex = INSERT_SIZE if l > INSERT_SIZE else l
    i = 0
    
    while eindex <= l:
        clist = nodes[sindex:eindex]
#         target_db.ping(True)
#         target_cursor.close()
#         target_cursor = target_db.cursor()
        try:
            
            target_cursor.executemany("INSERT ignore INTO " + tablename + "(sender, COUNT, children)VALUES(%s, %s,%s)",[(node['sender'], node['count'], str(node['children']) if node.has_key('children') else '') for node in clist ])
        except:
            target_db.close()
            target_db = MySQLdb.connect(host = target_host, port = target_port, user = target_user, passwd = target_passwd, db='work')
            target_cursor = target_db.cursor()
            target_cursor.executemany("INSERT ignore INTO " + tablename + "(sender, COUNT, children)VALUES(%s, %s,%s)",[(node['sender'], node['count'], str(node['children']) if node.has_key('children') else '') for node in clist ])
        target_db.commit()
        if eindex == l:
            break
        
        sindex = eindex
        eindex += INSERT_SIZE
        eindex = eindex if eindex <= l else l
        i += 1
        logging.info('index %d use time: %f' % (i, time.time() - start))
        start = time.time()
    
#     result.write(str(tree.tree))
    logging.info('insert db total time: %f' % (time.time() - start_2))
    print len(tree.tree)
    result.flush()
    result.close()
    smsFile.close()
    target_db.commit()
    target_cursor.close()
    target_db.close()
if __name__ == '__main__':
    print 'starting...'
    main()
        